<?php

$dir = dirname(__FILE__);                //查找当前脚本所在路径
require $dir . "/function.php";           //引入公共函数
require $dir . "/db_config.php";
require $dir . "/db.php";                 //引入数据库连接类
require $dir . "/Pdo.class.php";
require $dir . "/PHPExcel/PHPExcel.php";  //引入phpexcel类
//实例化db类，链接数据库
$objPHPExcel = new PHPExcel();          //实例化PHPExcel类，相当于在桌面新建一个excel
//获得当前活动单元格
$objSheet = $objPHPExcel->getActiveSheet();
//文字居中
$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置样式
//设置默认字体以及大小
$objSheet->getDefaultStyle()->getFont()->setName("微软雅黑")->setSize("14"); //
//设置年级所在行的字体大小
$objSheet->getStyle("A2:H2")->getFont()->setSize(20)->setBold(True);
//设置年级所在行字体大小并加粗
$objSheet->getStyle("A3:H3")->getFont()->setSize(16)->setBold(True);
//查询所有的班级
$gradeInfo = M($phpexcel)->q("SELECT distinct(grade) from stu_info order by id");
$index = 0;   //用于年级
foreach($gradeInfo as $k=>$v){
    //获得年级信息所在列
    $gradeIndex = getCells($index*2);
    $objSheet->setCellValue($gradeIndex."2",$v['grade']);
    //查询每个年级所有的班级
    $classInfo = M($phpexcel)->q("SELECT distinct(class) from stu_info where grade='".$v['grade'] . "'");
    foreach($classInfo as $c_k=>$c_v){
        //查询每个学生的信息
        $nameIndex = getCells($index * 2);     //获得每个班级学生姓名所在列的位置
        $ageIndex  = getCells($index * 2 + 1); //获得每个班级年龄所在列位置

        //合并每个班级的单元格
        $objSheet->mergeCells($nameIndex."3:" . $ageIndex . "3");
        //班级单元格的背景颜色
        $objSheet->getstyle($nameIndex . "3:" . $ageIndex . "3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB("52e351");
        //设置班级单元格的border
        $classBorderStyle = getBorderStyle('e351ca');
        $objSheet->getStyle($nameIndex . "3:" . $ageIndex . "3")->applyFromArray($classBorderStyle);     //设置班级所在列的border
        $info = M($phpexcel)->q("SELECT name,age from stu_info where grade='" . $v['grade'] ."'and class=" .$c_v['class'] );
        //填充班级信息
        $objSheet->setCellValue($nameIndex."3",$c_v['class'] . "班");
        //填充标题
        $objSheet->setCellValue($nameIndex."4","姓名")->setCellValue($ageIndex."4","年龄");

        //设置自动换行
        $objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true);
        $objSheet->setCellValue($nameIndex . "4","姓名\n换行")->setCellValue($ageIndex . "4" , "年龄");

        //填充学生信息
        $j = 5;
        foreach($info as $i_k=>$i_v){
            $objSheet->setCellValue($nameIndex.$j,$i_v['name'])->setCellValue($ageIndex.$j,$i_v['age']);
            $j++;
        }
        $index++;
    }
    //获得每个年级的终止单元格
    $endGradeIndex = getCells($index*2-1);
    //合并每个年级的单元格
    $objSheet->mergeCells($gradeIndex . "2:" . $endGradeIndex . "2");
    $objSheet->getstyle($gradeIndex . "2:" . $endGradeIndex . "2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB("e36951");
    //设置年级单元格的border
    //获取年级所在行的边框样式
    $gradeBorderStyle = getBorderStyle('e3df51');
    $objSheet->getStyle($gradeIndex . "2:" . $endGradeIndex . "2")->applyFromArray($gradeBorderStyle);
}







$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //生成excel文件
$objWriter->save($dir . "/excel.xls"); //保存文件


/**输出到浏览器**/
browser_export('Excel5', 'browser_excel03.xls');
$objWriter->save('php://output');

function browser_export($type, $filename)
{
    if ($type == "Excel5") {
        //告诉浏览器将要输出excel03文件
        header('Content-Type: application/vnd.ms-excel');
    } else {
        //告诉浏览器输出excel07文件
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    }
    //告诉浏览器将要输出文件的名字
    header('Content-Disposition: attachment;filename=' . $filename);
    //禁止缓存
    header('Cache-Control: max-age=0');
}
/**根据下标获得单元格所在列的位置**/
function getCells($index){
    $arr = range('A','Z');
    return $arr[$index];
}

/**边框样式**/
function getBorderStyle($color){
    $styleArray = array(
        'borders' => array(
            'outline' => array(
                'style' => PHPExcel_STYLE_Border::BORDER_THICK,
                'color' => array(
                    'rgb' => $color,
                ),
            ),
        ),
    );
    return $styleArray;
}



